<?php if ( ! defined('BASEPATH')) exit('No direct script access allowed');
require_once 'excel_reader2.php';
class Keuangan_mahasiswa_report extends MX_Controller {
    
  function __construct() {
//    $this->load->library('manimage');
    $this->load->model('frm/mfrm');
    $this->load->library('PHPExcel');
    $this->menu = $this->cek();
  }
  
  function ambil_kelebihan($id_keuangan_mahasiswa_jenis_virtual_account, $nim){
    if($this->input->post(NULL)){
      $this->global_models->get_connect("zikula");
//      $current = $this->global_models->get("academicportal_semester_tahun_ajaran_berjalan", array("is_running" => 1));
      $mahasiswa = $this->global_models->get_query("
        SELECT A.*
        FROM academicportal_student_categories AS A
        LEFT JOIN academicportal_mahasiswa AS B ON A.users = B.pn_uid
        WHERE B.nim = '{$nim}'");
//      $current = $this->global_models->get("academicportal_semester_tahun_ajaran_berjalan", array("is_running" => 1));
      $fakultas = $this->global_models->get_field("academicportal_course_categories", "parent", array("id" => $mahasiswa[0]->course_category));
      $this->global_models->get_connect("default");
      $pst = $this->input->post(NULL);
      if($pst['id_detail']){
        $kirim = array(
            "title"               => $pst['title'],
            "code"                => $pst['code'],
            "sort"                => $pst['sort'],
            "update_by_users"     => $this->session->userdata("id")
        );
        $id_keuangan_mahasiswa_jenis_biaya = $this->global_models->update("keuangan_mahasiswa_jenis_biaya", array("id_keuangan_mahasiswa_jenis_biaya" => $pst['id_detail']),$kirim);
      }
      else{
        $kelebihan = abs($this->mfrm->get_tunggakan_komponen($pst['nim'], $id_keuangan_mahasiswa_jenis_virtual_account));
        if($kelebihan >= $this->global_models->string_to_number($pst['saldo'])){
          $this->global_models->get_connect("zikula");
          $program_kuliah = $this->global_models->get("academicportal_mahasiswa", array("nim" => $pst['nim']));
          $this->global_models->get_connect("default");
          
          $current = $this->get_current_sta_prodi($program_kuliah);
          
          $kirim_tanggungan = array(
              "id_keuangan_mahasiswa_jenis_virtual_account"   => $id_keuangan_mahasiswa_jenis_virtual_account,
              "nim"                                           => $pst['nim'],
              "id_program_kuliah"                             => $program_kuliah[0]->program_kuliah,
              "id_kelompok"                                   => $program_kuliah[0]->kelompok_mahasiswa,
              "id_frm_account"                                => $pst['id_frm_account'],
              "notransaksi"                                   => $pst['notransaksi'],
              "saldo"                                         => $this->global_models->string_to_number($pst['saldo']),
              "pos"                                           => 3,
              "note"                                          => $pst['note'],
              "tahunsemester"       => $current[0]->tahun_ajaran.$current[0]->id,
              "create_date"                                   => $pst['tanggal'],
              "create_by_users"                               => $this->session->userdata("id"),
          );
          
          $id_keuangan_mahasiswa_tanggungan = $this->global_models->insert("keuangan_mahasiswa_tanggungan", $kirim_tanggungan);
          
          
          $kirim = array(
              "id_frm_journal"      => $this->mfrm->get_journal_current(),
              "notransaksi"         => $pst['notransaksi'],
              "saldo"               => $this->global_models->string_to_number($pst['saldo']),
              "tanggal"             => $pst['tanggal'],
              "note"                => "Pengambilan Kelebihan Bayar <a href='".site_url("keuangan-mahasiswa/kas-jenis-biaya-detail/{$id_keuangan_mahasiswa_jenis_virtual_account}/".$pst['nim'])."'>{$pst['nim']}</a>",
          );
              
          $id_pembayaran_mahasiswa = $this->nbscache->get_explode("variable", "id_pembalik_kelebihan_bayar");
            
          $id_frm_journal_log = $this->mfrm->create_journal_log($kirim);
          if($id_frm_journal_log){
            
            $debit[] = $this->global_models->string_to_number($pst['saldo']);
            $debit_da[] = $id_pembayaran_mahasiswa[1];

            $kredit[] = $this->global_models->string_to_number($pst['saldo']);
            $kredit_da[] = $pst['id_frm_account'];
            $this->mfrm->create_journal_log_account($this->mfrm->get_journal_current(), $id_frm_journal_log, $debit, $debit_da, $kredit, $kredit_da);
          }
//          $this->debug($this->mfrm->get_tunggakan_komponen($pst['nim'], $id_keuangan_mahasiswa_jenis_virtual_account), true);
        }
//        $this->debug($kelebihan);
//        $this->debug($this->global_models->string_to_number($pst['saldo']), true);
      }
      if($id_keuangan_mahasiswa_tanggungan){
        $this->session->set_flashdata('success', 'Data tersimpan');
      }
      else{
        $this->session->set_flashdata('notice', 'Data tidak tersimpan');
      }
      redirect("keuangan-mahasiswa/kas-jenis-biaya-detail/{$id_keuangan_mahasiswa_jenis_virtual_account}/".$pst['nim']);
    }
    else{
      
      $this->template->build("report/ambil-kelebihan",
        array(
              'url'         => base_url()."themes/".DEFAULTTHEMES."/",
              'menu'        => 'keuangan-mahasiswa/mahasiswa',
              'title'       => lang("keuangan_mahasiswa_ambil_kelebihan"),
              'detail'      => $detail,
              'nim'         => $nim,
              'id_keuangan_mahasiswa_jenis_virtual_account'         => $id_keuangan_mahasiswa_jenis_virtual_account
            ));
      $this->template
        ->set_layout('default')
        ->build("report/ambil-kelebihan");
    }
  }
  
  function ambil_kas_beasiswa($nim){
    if($this->input->post(NULL)){
      $pst = $this->input->post(NULL);
      if($pst['id_detail']){
        
      }
      else{
        $this->global_models->get_connect("zikula");
        $program_kuliah = $this->global_models->get("academicportal_mahasiswa", array("nim" => $pst['nim']));
        $this->global_models->get_connect("default");
        
        $semester = $this->get_current_sta_prodi($program_kuliah[0]->program_kuliah);
//        $this->debug($program_kuliah);
//        $this->debug($semester, true);
        $kirim = array(
            "nim"                 => $pst['nim'],
            "id_program_kuliah"   => $program_kuliah[0]->program_kuliah,
            "tanggal"             => $pst['tanggal'],
            "notransaksi"         => $pst['notransaksi'],
            "saldo"               => $this->global_models->string_to_number($pst['saldo']),
            "id_semester"         => $semester[0]->id,
            "tahun"               => $semester[0]->tahun_ajaran,
            "tahunsemester"       => $semester[0]->tahun_ajaran.$semester[0]->id,
            "note"                => $pst['note'],
            "pos"                 => 2,
            "create_date"         => date("Y-m-d H:i:s"),
            "create_by_users"     => $this->session->userdata("id"),
        );
        $id_keuangan_mahasiswa_penerima_beasiswa = $this->global_models->insert("keuangan_mahasiswa_penerima_beasiswa", $kirim);
        
        $kirim_journal = array(
            "id_frm_journal"      => $this->mfrm->get_journal_current(),
            "notransaksi"         => $pst['notransaksi'],
            "saldo"               => $this->global_models->string_to_number($pst['saldo']),
            "tanggal"             => $pst['tanggal'],
            "note"                => "Pembayaran Beasiswa <a href='".site_url("keuangan-mahasiswa/keuangan-mahasiswa-report/detail-penerima-beasiswa/".$pst['nim'])."'>{$pst['nim']}</a>",
        );
            
        $id_frm_journal_log = $this->mfrm->create_journal_log($kirim_journal);
        
        $id_kredit_beasiswa = $this->nbscache->get_explode("variable", "id_kredit_beasiswa");
        $id_debit_beasiswa = $this->nbscache->get_explode("variable", "id_debit_beasiswa");
        
        if($id_frm_journal_log){
          $debit[] = $this->global_models->string_to_number($pst['saldo']);
          $debit_da[] = $id_kredit_beasiswa[1];

          $kredit[] = $this->global_models->string_to_number($pst['saldo']);
          $kredit_da[] = $pst['id_frm_account'];
          $this->mfrm->create_journal_log_account($this->mfrm->get_journal_current(), $id_frm_journal_log, $debit, $debit_da, $kredit, $kredit_da);
        }
        
      }
      if($id_keuangan_mahasiswa_penerima_beasiswa){
        $this->session->set_flashdata('success', 'Data tersimpan');
      }
      else{
        $this->session->set_flashdata('notice', 'Data tidak tersimpan');
      }
      redirect("keuangan-mahasiswa/keuangan-mahasiswa-report/detail-penerima-beasiswa/".$pst['nim']);
    }
    else{
      
      $this->template->build("report/ambil-kas-beasiswa",
        array(
              'url'         => base_url()."themes/".DEFAULTTHEMES."/",
              'menu'        => 'keuangan-mahasiswa/mahasiswa',
              'title'       => lang("keuangan_mahasiswa_ambil_kas_beasiswa"),
              'detail'      => $detail,
              'nim'         => $nim,
            ));
      $this->template
        ->set_layout('default')
        ->build("report/ambil-kas-beasiswa");
    }
  }
  
  function bayar_kas_beasiswa($nim){
    if($this->input->post(NULL)){
      $pst = $this->input->post(NULL);
      if($pst['id_detail']){
        
      }
      else{
        $this->global_models->get_connect("zikula");
        $program_kuliah = $this->global_models->get("academicportal_mahasiswa", array("nim" => $pst['nim']));
        $id_category = $this->global_models->get_field("academicportal_program_kuliah", "category", array("id_program_kuliah" => $program_kuliah[0]->program_kuliah));
        $course_category_detail = $this->global_models->get("academicportal_course_categories", array("id" => $id_category));
        $course_category = $this->global_models->get_field("academicportal_course_categories", "id", array("kode_fak" => $course_category_detail[0]->kode_fak, "set_as_fakultas" => 1));
        $this->global_models->get_connect("default");
        
        $semester = $this->get_current_sta_prodi($program_kuliah[0]->program_kuliah);
//        $this->debug($program_kuliah);
//        $this->debug($semester, true);
        $kirim = array(
            "nim"                 => $pst['nim'],
            "id_keuangan_mahasiswa_jenis_virtual_account"   => $pst['id_keuangan_mahasiswa_jenis_virtual_account'],
            "id_program_kuliah"   => $program_kuliah[0]->program_kuliah,
            "tanggal"             => $pst['tanggal'],
            "notransaksi"         => $pst['notransaksi'],
            "saldo"               => $this->global_models->string_to_number($pst['saldo']),
            "id_semester"         => $semester[0]->id,
            "tahun"               => $semester[0]->tahun_ajaran,
            "tahunsemester"       => $semester[0]->tahun_ajaran.$semester[0]->id,
            "note"                => $pst['note'],
            "pos"                 => 2,
            "create_date"         => date("Y-m-d H:i:s"),
            "create_by_users"     => $this->session->userdata("id"),
        );
        $id_keuangan_mahasiswa_penerima_beasiswa = $this->global_models->insert("keuangan_mahasiswa_penerima_beasiswa", $kirim);
        
        $id_kredit_beasiswa = $this->nbscache->get_explode("variable", "id_kredit_beasiswa");
        
        $kirim_tanggungan = array(
            "id_keuangan_mahasiswa_jenis_virtual_account" => $pst['id_keuangan_mahasiswa_jenis_virtual_account'],
            "nim"                                         => $pst['nim'],
            "id_frm_account"                              => $id_kredit_beasiswa[1],
            "id_program_kuliah"                           => $program_kuliah[0]->program_kuliah,
            "id_kelompok"                                 => $program_kuliah[0]->kelompok_mahasiswa,
            "notransaksi"                                 => $pst['notransaksi'],
            "saldo"                                       => $this->global_models->string_to_number($pst['saldo']),
            "pos"                                         => 2,
            "id_semester"                                 => $semester[0]->id,
            "tahun"                                       => $semester[0]->tahun_ajaran,
            "tahunsemester"                               => $semester[0]->tahun_ajaran.$semester[0]->id,
            "create_date"                                 => date("Y-m-d H:i:s"),
            "create_by_users"                             => $this->session->userdata("id"),
            "note"                                        => "Pembayaran via Beasiswa <a href='".site_url("keuangan-mahasiswa/keuangan-mahasiswa-report/detail-penerima-beasiswa/".$pst['nim'])."'>{$pst['nim']}</a>",
        );
            
        $id_keuangan_mahasiswa_tanggungan = $this->global_models->insert("keuangan_mahasiswa_tanggungan", $kirim_tanggungan);
        $id_hutang_mahasiswa = $this->nbscache->get_explode("variable", "id_hutang_mahasiswa");
        
        $kirim_journal = array(
            "id_frm_journal"      => $this->mfrm->get_journal_current(),
            "notransaksi"         => $pst['notransaksi'],
            "saldo"               => $this->global_models->string_to_number($pst['saldo']),
            "tanggal"             => $pst['tanggal'],
            "note"                => "Pembayaran Tanggungan via Beasiswa <a href='".site_url("keuangan-mahasiswa/keuangan-mahasiswa-report/detail-penerima-beasiswa/".$pst['nim'])."'>{$pst['nim']}</a>",
        );
            
        $id_frm_journal_log = $this->mfrm->create_journal_log($kirim_journal);
        
        
        $piutang = $this->nbscache->get_explode("variable", "keuangan_mahasiswa_fakultas_beban_mahasiswa_".$course_category);
        
        if($id_frm_journal_log){
          $tanggungan_sisa = $this->mfrm->get_tanggungan_jenis_virtual($pst['id_keuangan_mahasiswa_jenis_virtual_account'], $pst['nim']);
          if($tanggungan_sisa <= 0){
            $debit[] = $this->global_models->string_to_number($pst['saldo']);
            $debit_da[] = $id_kredit_beasiswa[1];

            $kredit[] = $this->global_models->string_to_number($pst['saldo']);
            $kredit_da[] = $id_hutang_mahasiswa[1];
            $this->mfrm->create_journal_log_account($this->mfrm->get_journal_current(), $id_frm_journal_log, $debit, $debit_da, $kredit, $kredit_da);
          }
          else{
            if(($tanggungan_sisa - $this->global_models->string_to_number($pst['saldo'])) >= 0){
              $debit[] = $this->global_models->string_to_number($pst['saldo']);
              $debit_da[] = $id_kredit_beasiswa[1];

              $kredit[] = $this->global_models->string_to_number($pst['saldo']);
              $kredit_da[] = $piutang[1];
              $this->mfrm->create_journal_log_account($this->mfrm->get_journal_current(), $id_frm_journal_log, $debit, $debit_da, $kredit, $kredit_da);
            }
            else{
              $debit[] = $this->global_models->string_to_number($pst['saldo']);
              $debit_da[] = $id_kredit_beasiswa[1];

              $kredit[] = abs($total_tanggungan);
              $kredit_da[] = $piutang[1];
              $kredit[] = $this->global_models->string_to_number($pst['saldo']) - $total_tanggungan;
              $kredit_da[] = $id_hutang_mahasiswa[1];
              $this->mfrm->create_journal_log_account($this->mfrm->get_journal_current(), $id_frm_journal_log, $debit, $debit_da, $kredit, $kredit_da);
            }
          }
        }
        
      }
      if($id_keuangan_mahasiswa_penerima_beasiswa){
        $this->session->set_flashdata('success', 'Data tersimpan');
      }
      else{
        $this->session->set_flashdata('notice', 'Data tidak tersimpan');
      }
      redirect("keuangan-mahasiswa/keuangan-mahasiswa-report/detail-penerima-beasiswa/".$pst['nim']);
    }
    else{
      $virtual_account = $this->global_models->get_dropdown("keuangan_mahasiswa_jenis_virtual_account", "id_keuangan_mahasiswa_jenis_virtual_account", "title", FALSE);
      $this->template->build("report/bayar-kas-beasiswa",
        array(
              'url'         => base_url()."themes/".DEFAULTTHEMES."/",
              'menu'        => 'keuangan-mahasiswa/keuangan-mahasiswa-report/penerima-beasiswa',
              'title'       => lang("keuangan_mahasiswa_ambil_kas_beasiswa"),
              'detail'      => $detail,
              'nim'         => $nim,
              'virtual_account' => $virtual_account
            ));
      $this->template
        ->set_layout('default')
        ->build("report/bayar-kas-beasiswa");
    }
  }
  
  function pemasukan_harian(){
    
    if($this->input->post()){
      $pst = $this->input->post();
      if($pst['cari_prokul'] AND $pst['cari_tanggal']){
        $data = $this->global_models->get_query("
          SELECT *
          FROM keuangan_mahasiswa_tanggungan
          WHERE id_program_kuliah = {$pst['cari_prokul']} AND DATE_FORMAT(create_date, '%Y-%m-%d') = '{$pst['cari_tanggal']}' AND pos = 2
          ORDER BY nim ASC
          ");
      }
    }
    
    $this->global_models->get_connect("zikula");
    
    $prokul = $this->global_models->get_query("
      SELECT A.id_program_kuliah, B.name, A.nama_program
      FROM academicportal_program_kuliah AS A
      LEFT JOIN academicportal_course_categories AS B ON A.category = B.id
      ");
    
    $this->global_models->get_connect("default");
    
    foreach($prokul AS $pk){
      $prokul_drop[$pk->id_program_kuliah] = $pk->name." - ".$pk->nama_program;
    }
      
    $this->template->build("report/pemasukan-harian",
      array(
            'url'         => base_url()."themes/".DEFAULTTHEMES."/",
            'menu'        => 'keuangan-mahasiswa/keuangan-mahasiswa-report/pemasukan-harian',
            'title'       => lang("keuangan_mahasiswa_report_pemasukan_harian"),
            'data'        => $data,
            'prokul_drop' => $prokul_drop,
            'tanggal_pilih' => $pst['cari_tanggal'],
            'prokul_pilih' => $pst['cari_prokul'],
          ));
    $this->template
      ->set_layout('default')
      ->build("report/pemasukan-harian");
  }
  
  function keuangan_total(){
    
    if($this->input->post()){
      $pst = $this->input->post();
      if($pst['cari_prokul'] AND $pst['cari_sta'] AND $pst['cari_kelompok']){
        $data = $this->global_models->get_query("
          SELECT *
          FROM keuangan_mahasiswa_tanggungan
          WHERE id_program_kuliah = {$pst['cari_prokul']} AND tahunsemester <= {$pst['cari_sta']} 
            AND id_kelompok = {$pst['cari_kelompok']}
          GROUP BY nim
          ORDER BY nim ASC
          ");
      }
    }
    
    $this->global_models->get_connect("zikula");
    
    $prokul = $this->global_models->get_query("
      SELECT A.id_program_kuliah, B.name, A.nama_program
      FROM academicportal_program_kuliah AS A
      LEFT JOIN academicportal_course_categories AS B ON A.category = B.id
      ");
    $sta_dropdown_temp = $this->global_models->get_query("
      SELECT A. semester, A.tahun_ajaran, B.semester_name
      FROM academicportal_semester_tahun_ajaran AS A
      LEFT JOIN academicportal_semester AS B ON A.semester = B.id
      WHERE program_kuliah = {$prokul[0]->id_program_kuliah}");
      
    $kelompok_drop = $this->global_models->get_dropdown("academicportal_kelompok_mahasiswa", "id_kelompok_mahasiswa", "nama_kelompok", array(), FALSE);
      
    $this->global_models->get_connect("default");
    
    foreach($sta_dropdown_temp AS $sdt){
      $sta_dropdown[$sdt->tahun_ajaran.$sdt->semester] = $sdt->semester_name." - ".$sdt->tahun_ajaran."/".($sdt->tahun_ajaran + 1);
    }
    
    foreach($prokul AS $pk){
      $prokul_drop[$pk->id_program_kuliah] = $pk->name." - ".$pk->nama_program;
    }
      
    $this->template->build("report/keuangan-total",
      array(
            'url'         => base_url()."themes/".DEFAULTTHEMES."/",
            'menu'        => 'keuangan-mahasiswa/keuangan-mahasiswa-report/pemasukan-harian',
            'title'       => lang("keuangan_mahasiswa_report_keuangan_total"),
            'data'        => $data,
            'prokul_drop' => $prokul_drop,
            'sta_drop'   => $sta_dropdown,
            'kelompok_drop'   => $kelompok_drop,
            'sta_pilih' => $pst['cari_sta'],
            'prokul_pilih' => $pst['cari_prokul'],
            'kelompok_pilih' => $pst['cari_kelompok'],
          ));
    $this->template
      ->set_layout('default')
      ->build("report/keuangan-total");
  }
  
  function biaya_perkomponen(){
    
    if($this->input->post()){
      $pst = $this->input->post();
      if($pst['cari_prokul'] AND $pst['cari_sta'] AND $pst['cari_kelompok'] AND $pst['cari_komponen']){
        $data = $this->global_models->get_query("
          SELECT *
          FROM keuangan_mahasiswa_tanggungan
          WHERE id_program_kuliah = {$pst['cari_prokul']} AND tahunsemester <= {$pst['cari_sta']} 
            AND id_kelompok = {$pst['cari_kelompok']} AND id_keuangan_mahasiswa_jenis_virtual_account = {$pst['cari_komponen']}
          GROUP BY nim
          ORDER BY nim ASC
          ");
      }
    }
    
    $this->global_models->get_connect("zikula");
    
    $prokul = $this->global_models->get_query("
      SELECT A.id_program_kuliah, B.name, A.nama_program
      FROM academicportal_program_kuliah AS A
      LEFT JOIN academicportal_course_categories AS B ON A.category = B.id
      ");
    $sta_dropdown_temp = $this->global_models->get_query("
      SELECT A. semester, A.tahun_ajaran, B.semester_name
      FROM academicportal_semester_tahun_ajaran AS A
      LEFT JOIN academicportal_semester AS B ON A.semester = B.id
      WHERE program_kuliah = {$prokul[0]->id_program_kuliah}");
      
    $kelompok_drop = $this->global_models->get_dropdown("academicportal_kelompok_mahasiswa", "id_kelompok_mahasiswa", "nama_kelompok", array(), FALSE);
      
    $this->global_models->get_connect("default");
    
    foreach($sta_dropdown_temp AS $sdt){
      $sta_dropdown[$sdt->tahun_ajaran.$sdt->semester] = $sdt->semester_name." - ".$sdt->tahun_ajaran."/".($sdt->tahun_ajaran + 1);
    }
    
    foreach($prokul AS $pk){
      $prokul_drop[$pk->id_program_kuliah] = $pk->name." - ".$pk->nama_program;
    }
    
    $komponen_drop = $this->global_models->get_dropdown("keuangan_mahasiswa_jenis_virtual_account", "id_keuangan_mahasiswa_jenis_virtual_account", "title", FALSE);
      
    $this->template->build("report/biaya-komponen",
      array(
            'url'         => base_url()."themes/".DEFAULTTHEMES."/",
            'menu'        => 'keuangan-mahasiswa/keuangan-mahasiswa-report/biaya-perkomponen',
            'title'       => lang("keuangan_mahasiswa_report_biaya_perkomponen"),
            'data'        => $data,
            'prokul_drop' => $prokul_drop,
            'sta_drop'   => $sta_dropdown,
            'kelompok_drop'   => $kelompok_drop,
            'komponen_drop'   => $komponen_drop,
            'sta_pilih' => $pst['cari_sta'],
            'prokul_pilih' => $pst['cari_prokul'],
            'kelompok_pilih' => $pst['cari_kelompok'],
          ));
    $this->template
      ->set_layout('default')
      ->build("report/biaya-komponen");
  }
  
  function penerima_beasiswa(){
    
    if($this->input->post()){
      $pst = $this->input->post();
      if($pst['cari_prokul'] AND $pst['cari_kelompok']){
        $query = "";
        if($pst['cari_sta'])
          $query = " AND tahunsemester = {$pst['cari_sta']}";
        $data = $this->global_models->get_query("
          SELECT *
          FROM keuangan_mahasiswa_penerima_beasiswa
          WHERE id_program_kuliah = {$pst['cari_prokul']}
            AND id_kelompok = {$pst['cari_kelompok']}
              {$query}
          GROUP BY nim
          ORDER BY nim ASC
          ");
      }
    }
    
    $this->global_models->get_connect("zikula");
    
    $prokul = $this->global_models->get_query("
      SELECT A.id_program_kuliah, B.name, A.nama_program
      FROM academicportal_program_kuliah AS A
      LEFT JOIN academicportal_course_categories AS B ON A.category = B.id
      ");
    $sta_dropdown_temp = $this->global_models->get_query("
      SELECT A. semester, A.tahun_ajaran, B.semester_name
      FROM academicportal_semester_tahun_ajaran AS A
      LEFT JOIN academicportal_semester AS B ON A.semester = B.id
      WHERE program_kuliah = {$prokul[0]->id_program_kuliah}");
      
    $kelompok_drop = $this->global_models->get_dropdown("academicportal_kelompok_mahasiswa", "id_kelompok_mahasiswa", "nama_kelompok", FALSE, array());
      
    $this->global_models->get_connect("default");
    $sta_dropdown[""] = "- Pilih -";
    foreach($sta_dropdown_temp AS $sdt){
      $sta_dropdown[$sdt->tahun_ajaran.$sdt->semester] = $sdt->semester_name." - ".$sdt->tahun_ajaran."/".($sdt->tahun_ajaran + 1);
    }
    
    foreach($prokul AS $pk){
      $prokul_drop[$pk->id_program_kuliah] = $pk->name." - ".$pk->nama_program;
    }
    
      
    $this->template->build("report/penerima-beasiswa",
      array(
            'url'         => base_url()."themes/".DEFAULTTHEMES."/",
            'menu'        => 'keuangan-mahasiswa/keuangan-mahasiswa-report/penerima-beasiswa',
            'title'       => lang("keuangan_mahasiswa_report_penerima_beasiswa"),
            'data'        => $data,
            'prokul_drop' => $prokul_drop,
            'sta_drop'   => $sta_dropdown,
            'kelompok_drop'   => $kelompok_drop,
            'komponen_drop'   => $komponen_drop,
            'sta_pilih' => $pst['cari_sta'],
            'prokul_pilih' => $pst['cari_prokul'],
            'kelompok_pilih' => $pst['cari_kelompok'],
          ));
    $this->template
      ->set_layout('default')
      ->build("report/penerima-beasiswa");
  }
  
  function tagihan_tunggakan(){
    
    if($this->input->post()){
      $pst = $this->input->post();
      if($pst['cari_prokul'] AND $pst['cari_kelompok']){
        $data = $this->global_models->get_query("
          SELECT *
          FROM keuangan_mahasiswa_tanggungan
          WHERE id_program_kuliah = {$pst['cari_prokul']}
            AND id_kelompok = {$pst['cari_kelompok']}
          GROUP BY nim
          ORDER BY nim ASC
          ");
      }
    }
    
    $this->global_models->get_connect("zikula");
    
    $prokul = $this->global_models->get_query("
      SELECT A.id_program_kuliah, B.name, A.nama_program
      FROM academicportal_program_kuliah AS A
      LEFT JOIN academicportal_course_categories AS B ON A.category = B.id
      ");
    $sta_dropdown_temp = $this->global_models->get_query("
      SELECT A. semester, A.tahun_ajaran, B.semester_name
      FROM academicportal_semester_tahun_ajaran AS A
      LEFT JOIN academicportal_semester AS B ON A.semester = B.id
      WHERE program_kuliah = {$prokul[0]->id_program_kuliah}");
      
    $kelompok_drop = $this->global_models->get_dropdown("academicportal_kelompok_mahasiswa", "id_kelompok_mahasiswa", "nama_kelompok", array(), FALSE);
      
    $this->global_models->get_connect("default");
    
    foreach($sta_dropdown_temp AS $sdt){
      $sta_dropdown[$sdt->tahun_ajaran.$sdt->semester] = $sdt->semester_name." - ".$sdt->tahun_ajaran."/".($sdt->tahun_ajaran + 1);
    }
    
    foreach($prokul AS $pk){
      $prokul_drop[$pk->id_program_kuliah] = $pk->name." - ".$pk->nama_program;
    }
      
    $this->template->build("report/tagihan-tunggakan",
      array(
            'url'         => base_url()."themes/".DEFAULTTHEMES."/",
            'menu'        => 'keuangan-mahasiswa/keuangan-mahasiswa-report/tagihan-tunggakan',
            'title'       => lang("keuangan_mahasiswa_report_tagihan_tunggakan"),
            'data'        => $data,
            'prokul_drop' => $prokul_drop,
            'kelompok_drop'   => $kelompok_drop,
            'prokul_pilih' => $pst['cari_prokul'],
            'kelompok_pilih' => $pst['cari_kelompok'],
          ));
    $this->template
      ->set_layout('default')
      ->build("report/tagihan-tunggakan");
  }
  
  function pemasukan_persemester(){
    
    if($this->input->post()){
      $pst = $this->input->post();
      if($pst['cari_prokul'] AND $pst['cari_kelompok'] AND $pst['cari_sta']){
        $data = $this->global_models->get_query("
          SELECT *, sum(saldo) AS sld
          FROM keuangan_mahasiswa_tanggungan
          WHERE id_program_kuliah = {$pst['cari_prokul']}
            AND id_kelompok = {$pst['cari_kelompok']}
            AND tahunsemester <= {$pst['cari_sta']}
            AND pos = 2
          GROUP BY nim
          ORDER BY nim ASC
          ");
      }
    }
    
    $this->global_models->get_connect("zikula");
    
    $prokul = $this->global_models->get_query("
      SELECT A.id_program_kuliah, B.name, A.nama_program
      FROM academicportal_program_kuliah AS A
      LEFT JOIN academicportal_course_categories AS B ON A.category = B.id
      ");
    $sta_dropdown_temp = $this->global_models->get_query("
      SELECT A. semester, A.tahun_ajaran, B.semester_name
      FROM academicportal_semester_tahun_ajaran AS A
      LEFT JOIN academicportal_semester AS B ON A.semester = B.id
      WHERE program_kuliah = {$prokul[0]->id_program_kuliah}");
      
    $kelompok_drop = $this->global_models->get_dropdown("academicportal_kelompok_mahasiswa", "id_kelompok_mahasiswa", "nama_kelompok", array(), FALSE);
      
    $this->global_models->get_connect("default");
    
    foreach($sta_dropdown_temp AS $sdt){
      $sta_dropdown[$sdt->tahun_ajaran.$sdt->semester] = $sdt->semester_name." - ".$sdt->tahun_ajaran."/".($sdt->tahun_ajaran + 1);
    }
    
    foreach($prokul AS $pk){
      $prokul_drop[$pk->id_program_kuliah] = $pk->name." - ".$pk->nama_program;
    }
      
    $this->template->build("report/pemasukan-persemester",
      array(
            'url'         => base_url()."themes/".DEFAULTTHEMES."/",
            'menu'        => 'keuangan-mahasiswa/keuangan-mahasiswa-report/pemasukan-persemester',
            'title'       => lang("keuangan_mahasiswa_report_pemasukan_persemester"),
            'data'        => $data,
            'prokul_drop' => $prokul_drop,
            'sta_drop' => $sta_dropdown,
            'kelompok_drop'   => $kelompok_drop,
            'prokul_pilih' => $pst['cari_prokul'],
            'kelompok_pilih' => $pst['cari_kelompok'],
            'sta_pilih' => $pst['cari_sta'],
          ));
    $this->template
      ->set_layout('default')
      ->build("report/pemasukan-persemester");
  }
  
  function sign_program_kuliah(){
    $mahasiswa = $this->global_models->get("keuangan_mahasiswa_tanggungan");
    foreach($mahasiswa AS $mhs){
      $this->global_models->get_connect("zikula");
      $program_kuliah = $this->global_models->get("academicportal_mahasiswa", array("nim" => $mhs->nim));
      $this->global_models->get_connect("default");
      
      
      $this->global_models->update("keuangan_mahasiswa_tanggungan", array("id_keuangan_mahasiswa_tanggungan" => $mhs->id_keuangan_mahasiswa_tanggungan), array("id_program_kuliah" => $program_kuliah[0]->program_kuliah, "tahunsemester" => $mhs->tahun.$mhs->id_semester, "id_kelompok" => $program_kuliah[0]->kelompok_mahasiswa));
    }
  }
  
  private function get_current_sta_prodi($prokul = 0, $category_id = ""){
    $now = date("Y/m/d");
	
    $sql = "SELECT 	B.id AS id_sta,
					A.id,
					A.semester_name,
					A.semester_description,
					B.tahun_ajaran,
					B.semester_start,
					B.semester_finish,
					B.classreg_start,
					B.classreg_finish,
					B.classreg_add_drop_start,
					B.classreg_add_drop_finish,
					B.payment_start,
					B.payment_finish,
					B.payment_late_fee,
					B.grade_start,
					B.grade_finish,
					B.semester
			FROM	academicportal_semester AS A, academicportal_semester_tahun_ajaran AS B
			WHERE	B.program_kuliah = '$prokul'";
			
			if($category_id!=''){
				$sql .=" AND B.category = '$category_id'";
			}
			
			$sql .=" AND B.semester = A.id AND
					'$now' >= B.semester_start AND
					'$now' <= B.semester_finish";
      
      $this->global_models->get_connect("zikula");
      $data = $this->global_models->get_query($sql);
      $this->global_models->get_connect("default");
      return $data;
  }
  
  public function prokul_penerima_beasiswa(){
    $this->global_models->get_connect("zikula");
    
    $prokul = $this->global_models->get_query("
      SELECT A.id_program_kuliah, B.name, A.nama_program
      FROM academicportal_program_kuliah AS A
      LEFT JOIN academicportal_course_categories AS B ON A.category = B.id
      ");
      
    $this->global_models->get_connect("default");
    
//    $this->debug($prokul, true);
    
    $this->template->build("report/prokul-penerima-beasiswa",
      array(
            'url'         => base_url()."themes/".DEFAULTTHEMES."/",
            'menu'        => 'keuangan-mahasiswa/keuangan-mahasiswa-report/prokul-penerima-beasiswa',
            'title'       => "Pilih Batas Prodi-Program Kuliah",
            'course_category' => $prokul
          ));
    $this->template
      ->set_layout('default')
      ->build("report/prokul-penerima-beasiswa");
  }
  
  public function add_penerima_beasiswa($id_program_kuliah, $nim = ""){
    if($this->input->post(NULL)){
      $pst = $this->input->post(NULL);
      if($pst['id_detail']){
        
      }
      else{
        $this->global_models->get_connect("zikula");
        $kelompok = $this->global_models->get_field("academicportal_mahasiswa", "kelompok_mahasiswa", array("nim" => $pst['id_nim']));
        $this->global_models->get_connect("default");
        $kirim = array(
            "nim"                 => $pst['id_nim'],
            "id_program_kuliah"   => $id_program_kuliah,
            "id_kelompok"         => $kelompok,
            "tanggal"             => $pst['tanggal'],
            "notransaksi"         => $pst['notransaksi'],
            "saldo"               => $this->global_models->string_to_number($pst['saldo']),
            "id_semester"         => substr($pst['tahunsemester'], -1),
            "tahun"               => substr($pst['tahunsemester'], 0, -1),
            "tahunsemester"       => $pst['tahunsemester'],
            "note"                => $pst['note'],
            "pos"                 => 1,
            "create_date"         => date("Y-m-d H:i:s"),
            "create_by_users"     => $this->session->userdata("id"),
        );
        $id_keuangan_mahasiswa_penerima_beasiswa = $this->global_models->insert("keuangan_mahasiswa_penerima_beasiswa", $kirim);
        
        $kirim_journal = array(
            "id_frm_journal"      => $this->mfrm->get_journal_current(),
            "notransaksi"         => $pst['notransaksi'],
            "saldo"               => $this->global_models->string_to_number($pst['saldo']),
            "tanggal"             => $pst['tanggal'],
            "note"                => "Beasiswa <a href='".site_url("keuangan-mahasiswa/keuangan-mahasiswa-report/detail-penerima-beasiswa/".$pst['id_nim'])."'>{$pst['nim']}</a>",
        );
            
        $id_frm_journal_log = $this->mfrm->create_journal_log($kirim_journal);
        
        $id_kredit_beasiswa = $this->nbscache->get_explode("variable", "id_kredit_beasiswa");
        $id_debit_beasiswa = $this->nbscache->get_explode("variable", "id_debit_beasiswa");
        
        if($id_frm_journal_log){
          $debit[] = $this->global_models->string_to_number($pst['saldo']);
          $debit_da[] = $id_debit_beasiswa[1];

          $kredit[] = $this->global_models->string_to_number($pst['saldo']);
          $kredit_da[] = $id_kredit_beasiswa[1];
          $this->mfrm->create_journal_log_account($this->mfrm->get_journal_current(), $id_frm_journal_log, $debit, $debit_da, $kredit, $kredit_da);
        }
        
      }
      if($id_keuangan_mahasiswa_penerima_beasiswa){
        $this->session->set_flashdata('success', 'Data tersimpan');
      }
      else{
        $this->session->set_flashdata('notice', 'Data tidak tersimpan');
      }
      redirect("keuangan-mahasiswa/keuangan-mahasiswa-report/penerima-beasiswa");
    }
    else{
      $detail = $this->global_models->get("keuangan_mahasiswa_penerima_beasiswa", array("nim" => $nim));
      
      $this->global_models->get_connect("zikula");
      
      $sta_dropdown_temp = $this->global_models->get_query("
        SELECT A. semester, A.tahun_ajaran, B.semester_name
        FROM academicportal_semester_tahun_ajaran AS A
        LEFT JOIN academicportal_semester AS B ON A.semester = B.id
        WHERE program_kuliah = {$id_program_kuliah}");
      
      $this->global_models->get_connect("default");

      foreach($sta_dropdown_temp AS $sdt){
        $sta_dropdown[$sdt->tahun_ajaran.$sdt->semester] = $sdt->semester_name." - ".$sdt->tahun_ajaran."/".($sdt->tahun_ajaran + 1);
      }
      
      $this->template->build("report/add-penerima-beasiswa",
        array(
              'url'         => base_url()."themes/".DEFAULTTHEMES."/",
              'menu'        => 'keuangan-mahasiswa/keuangan-mahasiswa-report/penerima-beasiswa',
              'title'       => lang("keuangan_mahasiswa_penerima_beasiswa"),
              'detail'      => $detail,
              'nim'         => $nim,
              'id_program_kuliah' => $id_program_kuliah,
              'sta_dropdown' => $sta_dropdown,
            ));
      $this->template
        ->set_layout('default')
        ->build("report/add-penerima-beasiswa");
    }
	}
  
  function detail_penerima_beasiswa($nim){
    $total = $this->global_models->get_query("
      SELECT count(A.id_keuangan_mahasiswa_penerima_beasiswa) AS jumlah
      FROM keuangan_mahasiswa_penerima_beasiswa AS A WHERE A.nim = '{$nim}'
      ");
    $this->template->build("report/kas-beasiswa",
      array(
            'url'         => base_url()."themes/".DEFAULTTHEMES."/",
            'menu'        => 'keuangan-mahasiswa/keuangan-mahasiswa-report/penerima-beasiswa',
            'title'       => lang("keuangan_mahasiswa_kas"),
            'total'       => $total[0]->jumlah,
            'nim'         => $nim,
          ));
    $this->template
      ->set_layout('default')
      ->build("report/kas-beasiswa");
  }
  
  public function ajax_beasiswa($nim, $total = 0, $start = 0){
    
    
    $data = $this->global_models->get_query("
      SELECT A.*, B.title AS virtual_biaya
      FROM keuangan_mahasiswa_penerima_beasiswa AS A
      LEFT JOIN keuangan_mahasiswa_jenis_virtual_account AS B ON A.id_keuangan_mahasiswa_jenis_virtual_account = B.id_keuangan_mahasiswa_jenis_virtual_account
      WHERE A.nim = '{$nim}'
      ORDER BY A.create_date DESC LIMIT {$start},10
      ");
      
    $total_sebelum = $this->global_models->get_query("
      SELECT A.*
      FROM keuangan_mahasiswa_penerima_beasiswa AS A
      LEFT JOIN keuangan_mahasiswa_jenis_virtual_account AS B ON A.id_keuangan_mahasiswa_jenis_virtual_account = B.id_keuangan_mahasiswa_jenis_virtual_account
      WHERE A.nim = '{$nim}'
      ORDER BY A.create_date DESC LIMIT 0,{$start}
      ");
//    $this->debug($data);
//    $this->debug($total_sebelum, true);
    $total_debit = $this->global_models->get_query("
      SELECT SUM(A.saldo) AS saldo
      FROM keuangan_mahasiswa_penerima_beasiswa AS A
      WHERE A.nim = '{$nim}' AND (A.pos = 1)
      ");
      
    $total_kredit = $this->global_models->get_query("
      SELECT SUM(A.saldo) AS saldo
      FROM keuangan_mahasiswa_penerima_beasiswa AS A
      WHERE A.nim = '{$nim}' AND A.pos = 2
      ");
//    $this->debug($total_debit);
//    $this->debug($total_kredit, true);
    foreach ($total_sebelum AS $ts){
      if($ts->pos == 1){
        $total_debit_sebelum += $ts->saldo;
      }
      else{
        $total_kredit_sebelum += $ts->saldo;
      }
    }
    $saldo_sebelum = $total_debit_sebelum - $total_kredit_sebelum;
    $saldo_all = $total_debit[0]->saldo - $total_kredit[0]->saldo;
    $total_akhir = $saldo_all - $saldo_sebelum;
//    $this->debug($total_debit);
//    $this->debug($total_kredit, true);
//    $total_kredit = $this->global_models->get_field("frm_journal_log_account", "SUM(saldo)", 
//            array("id_frm_account" => $id_frm_account, "id_frm_journal" => $id_frm_journal, "pos" => 2, "tanggal >" => $data[0]->tanggal));
    
//    $pos_asli = $this->global_models->get_field("frm_account", "pos", array("id_frm_account" => $id_frm_account));
//    if($pos_asli == 1)
//      $total_akhir = $total_debit[0]->saldo - $total_kredit[0]->saldo;
//    else
//      $total_akhir = $total_kredit[0]->saldo - $total_debit[0]->saldo;
    
    foreach ($data as $key => $value) {
      if($value->pos == 1 OR $value->pos == 3){
        $saldo_debit = $value->saldo;
        $saldo_kredit = "";
        
      }
      else{
        $saldo_debit = "";
        $saldo_kredit = $value->saldo;
        
      }
      if($value->id_keuangan_mahasiswa_jenis_biaya){
        $title = "Tanggungan ".$value->jenis_biaya. " ". $value->id_semester." - ".$value->tahun;
      }
      else{
        $title = $value->note;
      }
      
      $this->global_models->get_connect("zikula");
      $semester_view = $this->global_models->get_field("academicportal_semester", "semester_name", array("id" => $value->id_semester));
      $this->global_models->get_connect("default");
      
      print "
      <tr class='ajax-quotation'>
        <td>{$value->create_date}</td>
        <td>{$semester_view} - {$value->tahun}</td>
        <td>".  $this->global_models->get_field("keuangan_mahasiswa_jenis_virtual_account", "title", array("id_keuangan_mahasiswa_jenis_virtual_account" => $value->id_keuangan_mahasiswa_jenis_virtual_account))."</td>
        <td>{$title}</td>
        <td>{$value->notransaksi}</td>
        <td style='text-align: right'>Rp ".  number_format($saldo_debit, 0, ",", ".")."</td>
        <td style='text-align: right'>Rp ".  number_format($saldo_kredit, 0, ",", ".")."</td>
        <td style='text-align: right; font-weight: bold;'>Rp ".  number_format($total_akhir, 0, ",", ".")."</td>
      </tr>";
      
      if($value->pos == 1 OR $value->pos == 3){
        $total_akhir = $total_akhir - $saldo_debit;
      }
      else{
        $total_akhir = $total_akhir + $saldo_kredit;
      }
    }
    
    $this->load->library('pagination');

    $config['base_url'] = '';
    $config['total_rows'] = $total;
    $config['per_page'] = 10; 
    $config['uri_segment'] = 6; 
    $config['cur_tag_open'] = "<li class='active'><a href='javascript:void(0)'>"; 
    $config['cur_tag_close'] = "</a></li>"; 
    $config['first_tag_open'] = "<li>"; 
    $config['first_tag_close'] = "</li>"; 
    $config['last_tag_open'] = "<li>"; 
    $config['last_tag_close'] = "</li>"; 
    $config['next_tag_open'] = "<li>"; 
    $config['next_tag_close'] = "</li>"; 
    $config['prev_tag_open'] = "<li>"; 
    $config['prev_tag_close'] = "</li>"; 
    $config['num_tag_open'] = "<li>"; 
    $config['num_tag_close'] = "</li>";
    $config['function_js'] = "load_quotation";

    $this->pagination->initialize($config); 
    print "
    <tr class='ajax-quotation'>
      <td colspan='8'>
        <div class='pagination'>
          <ul>
          ".$this->pagination->create_links_ajax()."
          </ul>
        </div>
      </td>
    </tr>";
    die;
  }
  
}

/* End of file welcome.php */
/* Location: ./application/controllers/welcome.php */